package app.book;

import app.controller.IndexController;
import app.util.Property;
import app.util.UserStrings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class BookDao {

    private final static Logger logger = LoggerFactory.getLogger(BookDao.class);

    public static Books getBooksByDate(String date) {
        Books books = new Books();
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(UserStrings.DBFileString);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate(UserStrings.DBTableCreateStatement);
            PreparedStatement prep = connection.prepareStatement(UserStrings.DBTableSelectDate);
            prep.setString(1, date);
            ResultSet rs = prep.executeQuery();
            while (rs.next()) {
                Book b = new Book();
                b.setName(rs.getString("bookname"));
                b.setReadDate(rs.getString("readdate"));
                b.setCategory(BookCategory.valueOf(rs.getString("bookcategory")));
                b.setId(rs.getString("id"));
                books.addBook(b);
            }
        } catch (SQLException e) {
            logger.error("SQL Error: "+e.toString());
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                logger.error("SQL Error: "+e.toString());
            }
        }
		return books;
    }

    public static int bookCountByDate(String date) {
        Connection connection = null;
        int count = 0;
        try {
            connection = DriverManager.getConnection(UserStrings.DBFileString);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate(UserStrings.DBTableCreateStatement);
            PreparedStatement prep = connection.prepareStatement(UserStrings.DBTableSelectDateCount);
            prep.setString(1, date);
            ResultSet rs = prep.executeQuery();
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        } catch (SQLException e) {
            logger.error("SQL Error: "+e.toString());
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                logger.error("SQL Error: "+e.toString());
            }
        }
        return count;
    }

    public static boolean addBookToDate(String date, String name, String category) {
        Connection connection = null;
        boolean ret = true;
        try {
            connection = DriverManager.getConnection(UserStrings.DBFileString);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate(UserStrings.DBTableCreateStatement);
            PreparedStatement prep = connection.prepareStatement(UserStrings.DBTableInsert);
            prep.setString(1, name);
            prep.setString(2, category);
            prep.setString(3, date);
            prep.executeUpdate();
        } catch (SQLException e) {
            logger.error("SQL Error: "+e.toString());
            ret = false;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                logger.error("SQL Error: "+e.toString());
                ret = false;
            }
        }
        return ret;
    }

    public static boolean delBookById(String date, String bookid) {
        Connection connection = null;
        boolean ret = true;
        try {
            connection = DriverManager.getConnection(UserStrings.DBFileString);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate(UserStrings.DBTableCreateStatement);
            PreparedStatement prep = connection.prepareStatement(UserStrings.DBTableDelete);
            prep.setString(1, bookid);
            prep.setString(2, date);
            prep.executeUpdate();
        } catch (SQLException e) {
            logger.error("SQL Error: "+e.toString());
            ret = false;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                logger.error("SQL Error: "+e.toString());
                ret = false;
            }
        }
        return ret;
    }

    public static List<Property> statisticBookByDate(String start, String end) {
        Map<String, Integer> map = new LinkedHashMap<>();
        for (BookCategory cat: BookCategory.values()) {
            map.put(cat.toString(), 0);
        }
        map.put("total", 0);
        List<Property> props = new ArrayList<>();
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(UserStrings.DBFileString);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate(UserStrings.DBTableCreateStatement);
            PreparedStatement prep = connection.prepareStatement(UserStrings.DBTableSelectDateRange);
            prep.setString(1, start);
            prep.setString(2, end);
            ResultSet rs = prep.executeQuery();
            while (rs.next()) {
                map.put("total", map.get("total") + 1);
                BookCategory cat = BookCategory.valueOf(rs.getString("bookcategory"));
                map.put(cat.toString(), map.get(cat.toString()) + 1);
            }
            for (String key: map.keySet()) {
                Property p = new Property(key, String.valueOf(map.get(key)));
                props.add(p);
            }
        } catch (SQLException e) {
            logger.error("SQL Error: "+e.toString());
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                logger.error("SQL Error: "+e.toString());
            }
        }
        return props;
    }

    public static List<Integer> getBooksByMonth() {
        List<Integer> books = new ArrayList<>();
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(UserStrings.DBFileString);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate(UserStrings.DBTableCreateStatement);
            PreparedStatement prep = connection.prepareStatement(UserStrings.DBTableSelectDateRangeCount);
            for (int i = 1; i <= 12; i++) {
                String startDate = String.format("%04d%02d01", LocalDate.now().getYear(), i);
                String endDate = String.format("%04d%02d31", LocalDate.now().getYear(), i);
                prep.setString(1, startDate);
                prep.setString(2, endDate);
                ResultSet rs = prep.executeQuery();
                int count = 0;
                while (rs.next()) {
                    count = rs.getInt("count(*)");
                }
                books.add(count);
            }
        } catch (SQLException e) {
            logger.error("SQL Error: "+e.toString());
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                logger.error("SQL Error: "+e.toString());
            }
        }
        return books;
    }
}
